package guet.dream.category;

import guet.dream.db.DatabaseUtils;
import guet.dream.question.Question;
import guet.dream.question.QuestionDao;
import guet.dream.question.QuestionDaoImpl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.Set;

public class CategoryDaoImpl implements CategoryDao {

	public Set<Category> findMainCategories() {
		
		Connection con = DatabaseUtils.getConnection();
		String queryString = "select * from categories where parent_id = ? ";
		Set<Category> categories = new HashSet<Category>();
		try {
			PreparedStatement pstmt = con.prepareStatement(queryString);
			pstmt.setInt(1, 0);
			ResultSet  rs = pstmt.executeQuery();
			while(rs.next()){
					int categoryCode = rs.getInt("category_code");
					String name =       rs.getString("name");
					String description = rs.getString("description");
					Category category = new Category(categoryCode, name, description);
					categories.add(category);
					findQuestions(category);
					findChildCategories(category);
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return categories;
	}

	public Set<Category> findChildCategories(Category category) {
		Connection con = DatabaseUtils.getConnection();
		String queryString = "select * from categories where parent_id = ?";
		Set<Category> categories = new HashSet<Category>();
		try {
			PreparedStatement pstmt = con.prepareStatement(queryString);
			pstmt.setInt(1, category.getCategoryCode());
			ResultSet  rs = pstmt.executeQuery();
			while(rs.next()){
					int categoryCode = rs.getInt("category_code");
					String name =       rs.getString("name");
					String description = rs.getString("description");
					Category childCategory = new Category(categoryCode, name, description);
					
					findQuestions(childCategory);
					findChildCategories(childCategory);
					
					categories.add(childCategory);
					category.addChildCategory(childCategory);
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return categories;
	}

	public Set<Question> findQuestions(Category category) {
		
		Set<Question> questions = new HashSet<Question>();
		
		Connection con = DatabaseUtils.getConnection();
		String queryString = "select * from question_category where category_code = ?";
		QuestionDao questionDao = new QuestionDaoImpl();
		PreparedStatement pstmt;
		try {
			pstmt = con.prepareStatement(queryString);
			pstmt.setInt(1, category.getCategoryCode());
			ResultSet rs = pstmt.executeQuery();
			
			while(rs.next()){
				int questionId = rs.getInt("question_id");
				
				Question question = questionDao.findById(questionId);
				questionDao.findAnswer(question);
				questions.add(question);	
				category.getQuestions().add(question);
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		category.setQuestions(questions);
		
		return questions;
	}

	/**
	 * for test in my system .this function is currently no need.
	 */
	public int save(Category category,int parentId) {
		int categoryCode = findMaxId()+1;
		String name = category.getName();
		String description = category.getDescription();
		
		Connection con = DatabaseUtils.getConnection();
		String queryString = "insert into categories(category_code,name,description,parent_id) values(?,?,?,?)";
		int count = 0;
		try {
			PreparedStatement pstmt = con.prepareStatement(queryString);
			pstmt.setInt(1, categoryCode);
			pstmt.setString(2, name);
			pstmt.setString(3, description);
			pstmt.setInt(4, parentId);
			
			count = pstmt.executeUpdate();
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		
		

		return count;
	}

	public int findMaxId() {
		
		int max = 0;
		Connection con = DatabaseUtils.getConnection();
		String queryString = "select max(category_code) from categories";
		ResultSet rs;
		try {
			Statement stmt = con.createStatement();
			rs = stmt.executeQuery(queryString);
			while(rs.next()){
				max = rs.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return max;
	}

	public int remove(int categoryCode) {
		Connection con = DatabaseUtils.getConnection();
		String queryString = "delete from categories where category_code = ? limit 1";
		int count = 0;
		try {
			PreparedStatement pstmt = con.prepareStatement(queryString);
			pstmt.setInt(1, categoryCode);
			count = pstmt.executeUpdate();
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return count;
	}

	public int update(Category category) {
		int categoryCode = category.getCategoryCode();
		String name = category.getName();
		String desc = category.getDescription();
		
		Connection con = DatabaseUtils.getConnection();
		String queryString = "update categories set name = ?,description = ? where category_code = ? limit 1";
		int count = 0;
		
		try {
			PreparedStatement pstmt = con.prepareStatement(queryString);
			
			pstmt.setString(1, name);
			pstmt.setString(2, desc);
			pstmt.setInt(3, categoryCode);
			
			count = pstmt.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return count;
	}

	public Category findCategoryById(int categoryCode) {
		Category category ;
		Connection con = DatabaseUtils.getConnection();
		String queryString  = "select * from categories where category_code = ?";
		String name = "",desc = "";
		try {
			PreparedStatement pstmt = con.prepareStatement(queryString);
			pstmt.setInt(1, categoryCode);
			ResultSet rs = pstmt.executeQuery();
			while(rs.next()){
				 name = rs.getString("name");
				 desc = rs.getString("description");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		category = new Category(categoryCode,name,desc);
		return category;
	}

}
